package club.guadazi.wemarket.util.excel;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.format.CellFormatType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;

public class ExcelUtils<E> {


    public static <E> List<String> getExcelHead(Class<E> e1Class) {
        List<String> list = new LinkedList<>();
        Field[] fields = e1Class.getDeclaredFields();
        if (fields.length > 0) {
            for (Field field : fields) {
                field.setAccessible(true);
                Excel excel = field.getAnnotation(Excel.class);
                if (excel != null) {
                    String excelName = excel.excelName();
                    list.add(excelName);
                }
            }
        } else {
            return null;
        }
        return list.size() > 0 ? list : null;
    }

    public static <E> boolean WriteDto2ExcelDoc(List<E> objects, OutputStream out) throws IOException {
        if (objects == null || objects.size() == 0)
            throw new IllegalArgumentException("DTO can not empty!");
        List<String> excelHead = getExcelHead(objects.get(0).getClass());
        List<Map<String, Object>> list = write2List(objects);
        write2Excel(excelHead, list, out);
        return true;
    }

    /**
     * @param heads
     * @param content
     * @param out
     * @throws Exception
     */
    public static void write2Excel(List<String> heads, List<Map<String, Object>> content, OutputStream out) throws IOException {
        if (heads == null || heads.isEmpty()) {
            throw new IllegalArgumentException("head tile can not empty!");
        }
        // 获取总列数
        final int CountColumnNum = heads.size();
        // 创建Excel文档
        HSSFWorkbook hwb = new HSSFWorkbook();
        // sheet 对应一个工作页
        HSSFSheet sheet = hwb.createSheet("Sheet 1");
        HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
        HSSFCell[] firstcell = new HSSFCell[CountColumnNum];
        String[] names = new String[0];
        names = heads.toArray(names);

        for (int j = 0; j < CountColumnNum; j++) {
            firstcell[j] = firstrow.createCell(j);
            firstcell[j].setCellValue(new HSSFRichTextString(names[j]));
        }
        if (content != null)
            for (int i = 0; i < content.size(); i++) {
                // 创建一行
                HSSFRow row = sheet.createRow(i + 1);
                // 得到要插入的每一条记录
                Map<String, Object> objectMap = content.get(i);
                for (int i1 = 0; i1 < heads.size(); i1++) {
                    HSSFCell xh = row.createCell(i1);
                    String title = heads.get(i1);
                    Object ov = objectMap.get(title);
                    String value = "";
                    if (ov != null) {
                        value = (String) ov;
                    }
                    xh.setCellValue(value);
                }
            }
        // 创建文件输出流，准备输出电子表格
        hwb.write(out);
        out.close();
        System.out.println("数据库导出成功");
    }

    public static <E> List<Map<String, Object>> write2List(List<E> objects) {
        Map<Field, String> fieldMap = new HashMap<>();
        {
            E e1 = objects.get(0);
            Class<?> e1Class = e1.getClass();
            Field[] fields = e1Class.getDeclaredFields();
            if (fields.length > 0) {
                for (Field field : fields) {
                    field.setAccessible(true);
                    Excel excel = field.getAnnotation(Excel.class);
                    if (excel != null) {//是要输出到excel
                        String excelName = excel.excelName();
                        fieldMap.put(field, excelName);
                    }
                }
            } else {
                return null;
            }
        }
        List<Map<String, Object>> list = new ArrayList<>();
        for (E object : objects) {
            Map<String, Object> map = new HashMap<String, Object>();
            Class<?> objectClass = object.getClass();
            Field[] fields = objectClass.getDeclaredFields();
            if (fields != null && fields.length > 0) {
                for (Field field : fields) {
                    field.setAccessible(true);
                    String name = fieldMap.get(field);
                    Object value = null;
                    try {
                        value = field.get(object);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    if (value != null) {
                        map.put(name, value);
                    }
                }
            }
            if (map.isEmpty()) {
                list.add(map);
            }
        }
        return list;
    }

    public static <E> List<E> read(InputStream is, Class<E> eClass) throws IOException, IllegalAccessException, InstantiationException {
        Map<String, Field> fieldMap = new HashMap<>();
        {
            Field[] fields = eClass.getDeclaredFields();
            if (fields.length > 0) {
                for (Field field : fields) {
                    field.setAccessible(true);
                    Excel excel = field.getAnnotation(Excel.class);
                    if (excel != null) {//是要输出到excel
                        String excelName = excel.excelName();
                        fieldMap.put(excelName, field);
                    }
                }
            } else {
                return null;
            }
        }


        // 创建Excel文档
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);


        List<E> list = new LinkedList<E>();


        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            Field[] fields = new Field[fieldMap.size()];
            //获取标题
            HSSFRow row = hssfSheet.getRow(0);
            for (int i = 0; i < fieldMap.size(); i++) {
                HSSFCell cell = row.getCell(i);
                String value = cell.getStringCellValue();
                Field field = fieldMap.get(value);
                fields[i] = field;
            }

            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }

                E e = eClass.newInstance();
                for (int i = 0; i < fieldMap.size(); i++) {
                    Field field = fields[i];
                    field.setAccessible(true);
                    Excel excel = field.getAnnotation(Excel.class);
                    ExcelCellDataTransfer transfer = excel.x2oTransfer();
                    HSSFCell cell = hssfRow.getCell(i);
                    Object o = null;
                    if (transfer == ExcelCellDataTransfer.Date) {
                        o = cell.getDateCellValue();
                    } else {
                        String value = getCellStringValue(cell);
                        o = value;
                        if (transfer != ExcelCellDataTransfer.NONE) {
                            o = transfer.parse(value);
                        }
                    }
                    field.set(e, o);
                }

                list.add(e);
            }
        }
        return list;
    }


    /**
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return
     */
    private static String getCellStringValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            cell.setCellType(Cell.CELL_TYPE_STRING);//都通过STRING类型来读取
            cellvalue = cell.getStringCellValue();
            if (StringUtils.isNotEmpty(cellvalue)) {
                cellvalue = cellvalue.trim();
                cellvalue = cellvalue.replace("\n", "");
                cellvalue = cellvalue.replace("\r", "");
                cellvalue = cellvalue.replace("\\", "/");//斜杠不能有
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }
}
